package org.addressBook.dao;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import org.addressBook.bean.PageInfo;
import org.addressBook.bean.TableData;
import org.addressBook.bean.TbUser;
import org.addressBook.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

// 编写操作数据库的类 JDBC
public class TbUserDao {
    // 注册功能实现
    // sql insert into tb_user (name,password)values(?,?)
    public void regist(String name, String mail, String password) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = JdbcUtils.getCon();
            String useDatabase = "USE" + " address_book" + ";";
            ps = con.prepareStatement(useDatabase);
            ps.executeUpdate(useDatabase);
            String sql = "insert into tb_user (name,mail,password)values(?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, mail);
            ps.setString(3, password);
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.close(null, ps, con);
        }
    }

    public TbUser login(String name, String password) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;

        try {
            TbUser user = new TbUser();
            con = JdbcUtils.getCon();
            String useDatabase = "USE" + " address_book" + ";";
            ps = con.prepareStatement(useDatabase);
            ps.executeUpdate(useDatabase);
            String sql1 = "SELECT * FROM tb_user WHERE NAME = ? AND PASSWORD = ?";
            String sql2 = "SELECT * FROM tb_user WHERE MAIL = ? AND PASSWORD = ?";
            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, name);
            ps1.setString(2, password);
            rs1 = ps1.executeQuery();

            while (rs1.next()) {
                user.setName(rs1.getString("name"));
                user.setMail(rs1.getString("mail"));
                user.setPassword(rs1.getString("password"));
            }

            ps2 = con.prepareStatement(sql2);
            ps2.setString(1, name);
            ps2.setString(2, password);
            rs2 = ps2.executeQuery();

            while (rs2.next()) {
                user.setName(rs2.getString("name"));
                user.setMail(rs2.getString("mail"));
                user.setPassword(rs2.getString("password"));
            }

            if (user.getName() == null) {
                return null;
            }
            return user;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.close(rs1, ps1, con);
            JdbcUtils.close(rs2, ps2, con);
        }
        return null;
    }

    public TbUser loadAddressBook(String name, String password) throws SQLException {

        // sql insert into tb_user (name,password)values(?,?)
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = JdbcUtils.getCon();
            String useDatabase = "USE" + " address_book" + ";";
            ps = con.prepareStatement(useDatabase);
            ps.executeUpdate(useDatabase);
            String table = name; // 用户名作为唯一标识符(主键、主码)，因此以用户名为新数据表的名字
            DatabaseMetaData metaData = con.getMetaData();
            ResultSet rs = metaData.getTables(null, null, table, new String[]{"TABLE"});
            if (!rs.next()) {
                String query = "CREATE TABLE " + table + " (" + "name varchar(20)," + "gender varchar(6)," + "mail varchar(30)," + "phone varchar(30)," + "address varchar(100)" + ")";
                ps.executeUpdate(query);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.close(null, ps, con);
        }
        return null;
    }

    //查询所有列表对象并分页
    //SELECT * FROM tb_user WHERE is_super = 0 LIMIT 0,5
    public PageInfo<TableData> list(String userName, Integer pageNum, Integer pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        PageInfo<TableData> page = new PageInfo<>();

        try {
            List<TableData> list = new ArrayList<>();
            con = JdbcUtils.getCon();
            String sql = "SELECT * FROM userName LIMIT ?,?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, (pageNum - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                //不能将对象放在循环的外部
                TableData data = new TableData();
                data.setName(rs.getString("name"));
                data.setGender(rs.getString("gender"));
                data.setMail(rs.getString("mail"));
                data.setPhone(rs.getString("phone"));
                data.setAddress(rs.getString("address"));
                list.add(data);
            }
            page.setResultList(list);
            page.setPageNum(pageNum);
            page.setPageSize(pageSize);
            return page;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                JdbcUtils.close(rs, ps, con);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    //统计条数
    private Integer count(int isSuper) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            List<TbUser> list = new ArrayList<>();
            con = JdbcUtils.getCon();
            String sql = "SELECT COUNT(*) FROM tb_user WHERE is_super = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, isSuper);
            rs = ps.executeQuery();
            ;
            rs.next();
            int count = rs.getInt(1);
            return count;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                JdbcUtils.close(rs, ps, con);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    public JsonObject jsonDao(String userName) throws SQLException, ClassNotFoundException {
        Connection con = null; // 定义引用
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 得到连接
            con = JdbcUtils.getCon();
            String sql = "select * from senguang";
            PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            /*            pstmt.setString(1,userName);*/
            rs = pstmt.executeQuery();
            if (rs == null) {
                return null;
            }
            Gson gson = new Gson();
            LinkedHashMap<String, String> hm = new LinkedHashMap<String, String>();
            JsonArray jsonArray = new JsonArray();
            JsonObject json = new JsonObject();
            String Shm = null;
            json.addProperty("code", 0);
            json.addProperty("msg", "");
            json.addProperty("count", 1000);
            while (rs.next()) {
                ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
                int icolnum = rsmd.getColumnCount();
                for (int i = 1; i < icolnum + 1; i++) {
                    // 转换为json类型
                    hm.put(rsmd.getColumnName(i).toString(), rs.getString(i));
                }
                jsonArray.add(gson.toJsonTree(hm));
/*                System.out.println(jsonArray);*/
            }
            json.add("data", jsonArray);
/*            System.out.println(json);*/

            return json;
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (con != null) con.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void PersonInfoDao(String oldName, String userName, String email, String aPassword) throws SQLException, ClassNotFoundException {
        Connection con = null; // 定义引用
        PreparedStatement pstmt = null;
        try {
            // 得到连接
            con = JdbcUtils.getCon();
            String sql = "UPDATE tb_user SET name=?, mail=?, password=? WHERE name=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, userName);
            pstmt.setString(2, email);
            pstmt.setString(3, aPassword);
            pstmt.setString(4, oldName);
            pstmt.executeUpdate();
        } finally {
            JdbcUtils.close(null, pstmt, con);
        }
    }
}
